﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace VarianceReportWinUI
{
    public class WorkItem
    {
        private Guid mKey;
        private string mName;
        private string mDescription;
        private DateTime? mCreated;
        private DateTime? mUpdated;
        private string mSource;

        private DataSet mWorkItemDataSet = null;

        public Guid Key { get { return mKey; } set { mKey = value; } }
        public string Name { get { return mName; } set { mName = value; } }
        public string Description { get { return mDescription; } set { mDescription = value; } }
        public DateTime? Created { get { return mCreated; } set { mCreated = value; } }
        public DateTime? Updated { get { return mUpdated; } set { mUpdated = value; } }
        public string Source { get { return mSource; } set { mSource = value; } }
        public DataSet WorkItemDataSet { get { return mWorkItemDataSet; } set { mWorkItemDataSet = value; } }

        public WorkItem()
        {
        }

        public WorkItem(string pSource)
        {
            mKey = Guid.NewGuid();
            mSource = pSource;
            mName = System.IO.Path.GetFileNameWithoutExtension(pSource);
            mCreated = DateTime.Now;

        }

        public System.Data.DataSet LoadExcelDataSet(bool pReload)
        {
            if (WorkItemDataSet == null || pReload)
            {
                WorkItemDataSet = new DataSet();

                using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mSource + ";Extended Properties=Excel 8.0"))
                {
                    con.Open();
                    try
                    {


                        string sheetname = "";

                        var dbSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                        if (dbSchema != null || dbSchema.Rows.Count > 0)
                        {
                            // Loop through all worksheets
                            for (int i = 0; i < dbSchema.Rows.Count; i++)
                            {
                                sheetname = dbSchema.Rows[i]["TABLE_NAME"].ToString();
                                break;
                            }
                        }

                        using (OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + sheetname + "$]", con))
                        {
                            myCommand.Fill(WorkItemDataSet);
                        }
                    }
                    catch (Exception ex)
                    {
                    }

                    con.Close();
                }

                WorkItemDataSet.Tables[0].Columns.Add("State", typeof(string));
                WorkItemDataSet.Tables[0].Columns.Add("UpdatedDate", typeof(DateTime));
            }

            return WorkItemDataSet;
        }

        public WorkItem Clone()
        {
            return (WorkItem)Serializer.FromXml(Serializer.ToXml(this, typeof(WorkItem)), typeof(WorkItem));
        }

        public void ExportToExcel()
        {
            //Excel.Range rng2 = this.Application.get_Range("A1", missing);
            //rng2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);


        }


    }
}
